﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MyLib.Data {
	/// <summary>
	/// MessagesProvider
	/// </summary>
	[System.ComponentModel.DesignerCategory("Code")]
	public class MessagesProvider : MyLib.DbContextProvider {
		/// <summary>
		/// FindMessages
		/// </summary>
		/// <param name="receiverId"></param>
		/// <param name="senderId"></param>
		/// <param name="title" ></param>
		/// <param name="content" ></param>
		/// <param name="hasRead"></param>
		/// <param name="status"></param>
		/// <param name="maximumRows"></param>
		/// <param name="startRowIndex"></param>
		/// <returns></returns>
		public System.Data.DataTable FindMessages(
			string receiverId = "",
			string senderId = "",
			string title = "",
			string content = "",
			bool? hasRead = null,
			int? status = null,
			int? maximumRows = null,
			int? startRowIndex = null) {
			var parameterReceiverId = new MyLib.DbParameter("receiver_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, receiverId);
			var parameterSenderId = new MyLib.DbParameter("sender_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, senderId);
			var parameterTitle = new MyLib.DbParameter("title", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, title);
			var parameterContent = new MyLib.DbParameter("content", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, content);
			var parameterHasRead = new MyLib.DbParameter("has_read", MyLib.DbParameterType.Integer, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, DBNull.Value);
			var parameterStatus = new MyLib.DbParameter("status", MyLib.DbParameterType.Integer, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, status);

			// Create sql.

			var sqlBuilder = new System.Text.StringBuilder();
			sqlBuilder.AppendLine(@"select
            msg.message_id messageId,
            msg.title,
            user1.`name` as receiverName,
            user2.`name` as senderName,
            msg.send_date as sendDate,
            msg.`status`,
            coalesce(mu.has_read , 0) as hasRead
            from
            msg_messages msg
            left join sec_user user1 ON msg.receiver_id = user1.user_id
            left join sec_user user2 ON msg.sender_id = user2.user_id");

			if (string.IsNullOrEmpty(receiverId)) {
				sqlBuilder.AppendLine("left join msg_user mu ON msg.message_id = mu.message_id");
			} else {
				sqlBuilder.AppendLine("left join msg_user mu ON msg.message_id = mu.message_id and mu.`user_id` = #receiver_id#");
			}

			// Create dynamic sql 1.

			var dynamicFlag1 = false;
			var dynamicText1 = new System.Text.StringBuilder();

			if (!string.IsNullOrEmpty(receiverId)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"(msg.receiver_id = #receiver_id# or msg.receiver_id is null)");

				dynamicFlag1 = true;
			}

			if (!string.IsNullOrEmpty(senderId)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"msg.sender_id = #sender_id#");

				dynamicFlag1 = true;
			}

			if (!string.IsNullOrEmpty(title)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"msg.`title` like concat('%', #title# , '%')");

				dynamicFlag1 = true;
			}

			if (!string.IsNullOrEmpty(content)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"msg.`content` like concat('%', #content# , '%')");

				dynamicFlag1 = true;
			}

			if (hasRead.HasValue) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"coalesce(mu.has_read, 0) = #has_read#");

				parameterHasRead.Value = hasRead.Value ? 1 : 0;

				dynamicFlag1 = true;
			}

			if (status.HasValue) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"msg.`status` = #status#");

				dynamicFlag1 = true;
			}

			if (dynamicFlag1) {
				sqlBuilder.Append(" where ");
				sqlBuilder.Append(dynamicText1.ToString());
			}

			sqlBuilder.AppendLine(@"order by msg.send_date desc");

			// Sql text.
			var sql = sqlBuilder.ToString();

			using (var session = SessionFactory.CreateReadOnlySession()) {
				return session.ExecutePagedQuery(sql, System.Data.CommandType.Text, startRowIndex ?? 0, maximumRows ?? int.MaxValue,
					parameterReceiverId,
					parameterSenderId,
					parameterTitle,
					parameterContent,
					parameterHasRead,
					parameterStatus);
			}
		} // end of FindMessages

		/// <summary>
		/// FindMessagesCount
		/// </summary>
		/// <param name="title" ></param>
		/// <param name="content" ></param>
		/// <param name="receiverId"></param>
		/// <param name="senderId"></param>
		/// <param name="hasRead"></param>
		/// <param name="status"></param>
		/// <returns></returns>
		public int FindMessagesCount(
			string receiverId = "",
			string senderId = "",
			string title = "",
			string content = "",
			bool? hasRead = null,
			int? status = null) {
			var parameterReceiverId = new MyLib.DbParameter("receiver_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, receiverId);
			var parameterSenderId = new MyLib.DbParameter("sender_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, senderId);
			var parameterTitle = new MyLib.DbParameter("title", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, title);
			var parameterContent = new MyLib.DbParameter("content", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, content);
			var parameterHasRead = new MyLib.DbParameter("has_read", MyLib.DbParameterType.Integer, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, DBNull.Value);
			var parameterStatus = new MyLib.DbParameter("status", MyLib.DbParameterType.Integer, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, status);

			// Create sql.

			var sqlBuilder = new System.Text.StringBuilder();
			sqlBuilder.AppendLine(@"select count(*)
			from
            msg_messages msg
            left join sec_user user1 ON msg.receiver_id = user1.user_id
            left join sec_user user2 ON msg.sender_id = user2.user_id");

			if (string.IsNullOrEmpty(receiverId)) {
				sqlBuilder.AppendLine("left join msg_user mu ON msg.message_id = mu.message_id");
			} else {
				sqlBuilder.AppendLine("left join msg_user mu ON msg.message_id = mu.message_id and mu.`user_id` = #receiver_id#");
			}

			// Create dynamic sql 1.

			var dynamicFlag1 = false;
			var dynamicText1 = new System.Text.StringBuilder();

			if (!string.IsNullOrEmpty(receiverId)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"(msg.receiver_id = #receiver_id# or msg.receiver_id is null)");

				dynamicFlag1 = true;
			}

			if (!string.IsNullOrEmpty(senderId)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"msg.sender_id = #sender_id#");

				dynamicFlag1 = true;
			}

			if (!string.IsNullOrEmpty(title)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"msg.`title` like concat('%', #title# , '%')");

				dynamicFlag1 = true;
			}

			if (!string.IsNullOrEmpty(content)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"msg.`content` like concat('%', #content# , '%')");

				dynamicFlag1 = true;
			}

			if (hasRead.HasValue) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"coalesce(mu.has_read, 0) = #has_read#");
				parameterHasRead.Value = hasRead.Value ? 1 : 0;

				dynamicFlag1 = true;
			}

			if (status.HasValue) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"msg.`status` = #status#");

				dynamicFlag1 = true;
			}

			if (dynamicFlag1) {
				sqlBuilder.Append(" where ");
				sqlBuilder.Append(dynamicText1.ToString());
			}

			// Sql text.

			var sql = sqlBuilder.ToString();

			using (var session = SessionFactory.CreateReadOnlySession()) {
				return session.ExecuteScalar<int>(sql, System.Data.CommandType.Text,
					parameterReceiverId,
					parameterSenderId,
					parameterTitle,
					parameterContent,
					parameterHasRead,
					parameterStatus);
			}
		} // end of FindMessagesCount

		/// <summary>
		/// FindById
		/// </summary>
		/// <param name="messageId" ></param>
		/// <returns></returns>
		public System.Data.DataTable FindById(string messageId) {
			if (string.IsNullOrEmpty(messageId)) {
				return null;
			}

			if (messageId == "0") {
				var dt = new System.Data.DataTable();
				dt.Columns.Add("messageId", typeof(string));
				dt.Columns.Add("title", typeof(string));
				dt.Columns.Add("receiverId", typeof(string));
				dt.Columns.Add("senderId", typeof(string));
				dt.Columns.Add("sendDate", typeof(DateTime));
				dt.Columns.Add("content", typeof(string));
				dt.Columns.Add("status", typeof(int));

				var row = dt.NewRow();
				row["status"] = 0;

				dt.Rows.Add(row);

				dt.AcceptChanges();
				return dt;
			} else {
				var parameterMessageId = new MyLib.DbParameter("message_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, messageId);

				// Create sql.

				var sql = @"select
            msg.message_id messageId,
            msg.title,
			msg.receiver_id as receiverId,
            user1.`name` as receiverName,
			msg.sender_id as senderId,
            user2.`name` as senderName,
            msg.send_date sendDate,
            msg.content,
            msg.`status`
            from
            msg_messages msg
            left join
            sec_user user1 ON msg.receiver_id = user1.user_id
            left join
            sec_user user2 ON msg.sender_id = user2.user_id
            where
            msg.message_id = #message_id#";

				using (var session = SessionFactory.CreateReadOnlySession()) {
					return session.ExecuteQuery(sql, System.Data.CommandType.Text, parameterMessageId);
				}
			}
		} // end of FindById

		/// <summary>
		/// 
		/// </summary>
		/// <param name="messageId"></param>
		/// <param name="receiverId"></param>
		/// <returns></returns>
		public System.Data.DataTable ReadById(string messageId, string receiverId) {
			var parameterMessageId = new MyLib.DbParameter("message_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, messageId);
			var parameterReceiverId = new MyLib.DbParameter("receiver_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, receiverId);

			var sql1 = @"select
            msg.message_id messageId,
            msg.title,
			msg.receiver_id as receiverId,
            user1.`name` as receiverName,
			msg.sender_id as senderId,
            user2.`name` as senderName,
            msg.send_date sendDate,
            msg.content,
            msg.`status`
            from
            msg_messages msg
            left join
            sec_user user1 ON msg.receiver_id = user1.user_id
            left join
            sec_user user2 ON msg.sender_id = user2.user_id
            where
            msg.message_id = #message_id#";

			var sql2 = @"msg_mark_message_has_read";

			using (var session = SessionFactory.CreateSession()) {
				// Find by message id.
				var dt = session.ExecuteQuery(sql1, System.Data.CommandType.Text, parameterMessageId);

				if (!string.IsNullOrEmpty(messageId)) {
					// Mark as read.
					session.ExecuteUpdate(sql2, System.Data.CommandType.StoredProcedure,
						parameterMessageId,
						parameterReceiverId);

					session.Commit();
				}

				return dt;
			}
		} // end of ReadById.

		/// <summary>
		/// 
		/// </summary>
		/// <param name="messageId"></param>
		/// <param name="sendDate"></param>
		/// <returns></returns>
		public int Send(string messageId, DateTime? sendDate = null) {
			var parameterMessageId = new MyLib.DbParameter("message_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, messageId);
			var parameterSendDate = new MyLib.DbParameter("send_date", MyLib.DbParameterType.DateTime, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, sendDate ?? DateTime.Now);

			var sql = @"update msg_messages set `status` = 1, send_date = #send_date# where message_id = #message_id# and `status` = 0";

			using (var session = SessionFactory.CreateSession()) {
				var c = session.ExecuteUpdate(sql, System.Data.CommandType.Text,
					parameterMessageId,
					parameterSendDate);

				session.Commit();

				return c;
			}
		} // end of Send.

		/// <summary>
		/// Save
		/// </summary>
		/// <param name="messageId" ></param>
		/// <param name="title" ></param>
		/// <param name="receiverId"></param>
		/// <param name="senderId" ></param>
		/// <param name="sendDate" ></param>
		/// <param name="content" ></param>
		/// <param name="status"></param>
		/// <returns></returns>
		public int Save(
			ref string messageId,
			string title,
			string senderId,
			string receiverId,
			System.DateTime? sendDate,
			string content,
			int status) {

			System.Diagnostics.Debug.Assert(!string.IsNullOrEmpty(senderId));

			// Create sql.

			const string sql1 = @"insert into msg_messages
            (message_id, 
            title, 
            receiver_id,
            sender_id, 
            send_date, 
            content, 
            `status`)
            values(
            #message_id#, 
            #title#, 
            #receiver_id#, 
            #sender_id#, 
            #send_date#, 
            #content#, 
            0);";

			const string sql2 = @"update msg_messages
            set 
            title = #title#,
            receiver_id = #receiver_id#,
            sender_id = #sender_id#,
            send_date = #send_date#,
            content = #content#
            where message_id = #message_id#
			and `status` = 0";

			var parameterMessageId = new MyLib.DbParameter("message_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, messageId);
			var parameterTitle = new MyLib.DbParameter("title", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, title);
			var parameterReceiverId = new MyLib.DbParameter("receiver_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, receiverId);
			var parameterSenderId = new MyLib.DbParameter("sender_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, senderId);
			var parameterSendDate = new MyLib.DbParameter("send_date", MyLib.DbParameterType.DateTime, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, sendDate);
			var parameterContent = new MyLib.DbParameter("content", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, content);

			using (var session = SessionFactory.CreateSession()) {
				// Do query.

				int c;
				if (string.IsNullOrEmpty(messageId)) {
					messageId = IdGenerator.NewId("msg_messages").ToString();
					parameterMessageId.Value = messageId;

					c = session.ExecuteUpdate(sql1, System.Data.CommandType.Text,
						parameterMessageId,
						parameterTitle,
						parameterReceiverId,
						parameterSenderId,
						parameterSendDate,
						parameterContent);
				} else {
					c = session.ExecuteUpdate(sql2, System.Data.CommandType.Text,
						parameterMessageId,
						parameterTitle,
						parameterReceiverId,
						parameterSenderId,
						parameterSendDate,
						parameterContent);
				}

				session.Commit();

				return c;
			}
		} // end of Save.

		/// <summary>
		/// Delete
		/// </summary>
		/// <param name="messageId" ></param>
		/// <returns></returns>
		public int Delete(string messageId) {
			var parameterMessageId = new MyLib.DbParameter("message_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, messageId);

			var sql = @"delete from msg_messages where message_id = #message_id# and `status` = 0";

			using (var session = SessionFactory.CreateSession()) {
				var c = session.ExecuteUpdate(sql, System.Data.CommandType.Text, parameterMessageId);

				session.Commit();

				return c;
			}
		} // end of Delete.
	} // end of MessagesProvider.
}
